import pymysql
from db import config


class Model:
    tab_name = None
    link = None
    cursor = None
    pk = None
    fields = []

    def __init__(self, table):
        try:
            self.tab_name = table
            self.link = pymysql.connect(host=config.HOST, user=config.USER, password=config.PASS_WD, db=config.DB_NAME,
                                        charset="utf8", port=config.PORT)
            self.cursor = self.link.cursor(pymysql.cursors.DictCursor)
            self.field()
        except Exception as err:
            print('Model类初始化报错，原因：%s' % err)

    def field(self):
        try:
            sql = "show columns from %s" % self.tab_name
            self.cursor.execute(sql)
            flist = self.cursor.fetchall()
            for e in flist:
                # 收集每个字段名称
                self.fields.append(e['Field'])
                # 判断并收集表的主键名称
                if e['Key'] == 'PRI':
                    self.pk = e['Field']
            return self.fields

        except Exception as err:

            print('表结构获取失败，原因：%s' % err)

    def findall(self):
        try:
            sql = 'select * from %s' % self.tab_name
            self.cursor.execute(sql)
            return self.cursor.fetchall()

        except Exception as err:
            print('SQL查询执行错误，原因：%s' % err)
            return []

    def find(self, id=0):
        try:
            sql = "select * from %s where %s=%s" % (self.tab_name, self.pk, id)
            self.cursor.execute(sql)
            return self.cursor.fetchone()
        except Exception as err:
            print('SQL查询执行错误，原因：%s' % err)
            return None

    def select(self, where=[], order=None, limit=None):
        try:
            sql = "select * from %s" % self.tab_name
            if isinstance(where, list) and len(where):
                sql += ' where ' + ' and '.join(where)
            if order != None:
                sql += ' order by %s' % order
            if limit != None:
                sql += ' limit %s' % str(limit)

            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as err:
            print('SQL查询执行错误，原因：%s' % err)
            return []

    def add(self, data={}):
        try:
            keys = []
            values = []
            if isinstance(data, dict) and len(data):
                for k, v in data.items():
                    if k in self.fields:
                        keys.append(k)
                        values.append(v)
            else:
                print('data必须为dict类型')
                return 0
            sql = "insert into %s(%s) values(%s)" % (self.tab_name, ','.join(keys), ','.join(['%s'] * len(values)))
            self.cursor.execute(sql, values)
            self.link.commit()
            return self.cursor.lastrowid
        except Exception as err:
            self.link.rollback()
            print('SQL添加执行错误，原因：%s' % err)
            return 0

    def update(self, data={}):
        try:
            values = []
            if isinstance(data, dict) and len(data):
                for k, v in data.items():
                    if k in self.fields and k != self.pk:
                        values.append("%s='%s'" % (k, v))
            else:
                print('data必须为dict类型')
                return 0

            sql = "update %s set %s where %s=%s" % (self.tab_name, ','.join(values), self.pk, data.get(self.pk))
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.rowcount

        except Exception as err:
            self.link.rollback()
            print('SQL修改执行错误，原因：%s' % err)
            return 0

    def delete(self, id=None):
        try:
            if id:
                sql = "delete from %s where %s=%s" % (self.tab_name, self.pk, id)
                self.cursor.execute(sql)
                self.link.commit()
                return self.cursor.rowcount
            else:
                print('ID不能为空')

        except Exception as err:
            self.link.rollback()
            print('SQL删除执行错误，原因：%s' % err)
            return 0

    def __del__(self):
        if self.link:
            self.link.close()
        if self.cursor:
            self.cursor.close()
